﻿using Longbow.Cache;
using Longbow.Data;
using PetaPoco;
using System;
using System.Collections.Generic;
using System.Linq;

namespace Bootstrap.Security.DataAccess
{
    /// <summary>
    /// Bootstrap Admin 后台管理数据库操作类，内部使用 ba 连接字符串进行数据库连接
    /// </summary>
    public static class DbHelper
    {
        /// <summary>
        /// 通过当前用户名获取所有角色缓存键名称 "RoleHelper-RetrieveRolesByUserName"
        /// </summary>
        public const string RetrieveRolesByUserNameDataKey = "RoleHelper-RetrieveRolesByUserName";

        /// <summary>
        /// 通过当前访问菜单地址获取所有角色缓存键名称 "RoleHelper-RetrieveRolesByUrl"
        /// </summary>
        public const string RetrieveRolesByUrlDataKey = "RoleHelper-RetrieveRolesByUrl";

        /// <summary>
        /// 通过当前用户名获取所有授权应用缓存键名称 "AppHelper-RetrieveAppsByUserName"
        /// </summary>
        public const string RetrieveAppsByUserNameDataKey = "AppHelper-RetrieveAppsByUserName";

        /// <summary>
        /// 通过当前用户名获取用户数据缓存键名称 "BootstrapUser-RetrieveUsersByName"
        /// </summary>
        public const string RetrieveUsersByNameDataKey = "BootstrapUser-RetrieveUsersByName";

        /// <summary>
        /// 通过当前用户获取所有菜单数据缓存键名称 "BootstrapMenu-RetrieveMenus"
        /// </summary>
        public const string RetrieveMenusAll = "BootstrapMenu-RetrieveMenus";

        /// <summary>
        /// 获取所有字典数据缓存键名称 "BootstrapMenu-RetrieveMenus"
        /// </summary>
        public const string RetrieveDictsDataKey = "BootstrapDict-RetrieveDicts";

        /// <summary>
        /// 通过当前用户获取所有组数据缓存键名称 "GroupHelper-RetrieveGroupsByUserName"
        /// </summary>
        public const string RetrieveGroupsByUserNameDataKey = "GroupHelper-RetrieveGroupsByUserName";

        /// <summary>
        /// 获取所有组数据缓存键名称 "GroupHelper-RetrieveGroups"
        /// </summary>
        public const string RetrieveGroupsDataKey = "GroupHelper-RetrieveGroups";

        /// <summary>
        /// 获得带层次关系的菜单集合
        /// </summary>
        /// <param name="navs">未层次化菜单集合</param>
        /// <param name="url">设置此菜单状态为 active</param>
        /// <returns>带层次化的菜单集合</returns>
        public static IEnumerable<BootstrapMenu> CascadeMenus(IEnumerable<BootstrapMenu> navs, string url = null)
        {
            var root = navs.Where(m => m.ParentId == "0").OrderBy(m => m.Category).ThenBy(m => m.Order).ToList();
            CascadeMenus(navs, null, root, url);
            return root;
        }

        private static void CascadeMenus(IEnumerable<BootstrapMenu> navs, BootstrapParentMenu parent, IEnumerable<BootstrapMenu> level, string url = null)
        {
            level.ToList().ForEach(m =>
            {
                m.Menus = navs.Where(sub => sub.ParentId == m.Id).OrderBy(sub => sub.Order);
                if (!string.IsNullOrEmpty(url))
                {
                    // Active menu
                    m.Active = m.Url.Equals(url, StringComparison.OrdinalIgnoreCase) ? "active" : "";
                    var pm = parent;
                    while (pm != null && m.Active != "")
                    {
                        pm.Current.Active = m.Active;
                        pm = pm.Parent;
                    }
                }
                CascadeMenus(navs, new BootstrapParentMenu(parent, m), m.Menus, url);
            });
        }

        private static void ActiveMenu(BootstrapParentMenu parent, IEnumerable<BootstrapMenu> menus, string url)
        {
            if (menus == null || !menus.Any() || string.IsNullOrEmpty(url))
            {
                return;
            }

            menus.AsParallel().ForAll(m =>
            {
                m.Active = m.Url.Equals(url, StringComparison.OrdinalIgnoreCase) ? "active" : "";
                ActiveMenu(new BootstrapParentMenu(parent, m), m.Menus, url);
                if (parent != null && m.Active != "")
                {
                    parent.Current.Active = m.Active;
                }
            });
        }

        /// <summary>
        /// 在菜单集合中查找指定 Url 的菜单并设置 Active
        /// </summary>
        /// <param name="menus">菜单集合（兼容层次化集合）</param>
        /// <param name="url">指定菜单地址</param>
        public static void ActiveMenu(IEnumerable<BootstrapMenu> menus, string url) => ActiveMenu(null, menus, url);

        /// <summary>
        /// 获得指定用户名可访问的所有菜单集合
        /// </summary>
        /// <param name="userName">当前用户名</param>
        /// <returns>未层次化的菜单集合</returns>
        public static IEnumerable<BootstrapMenu> RetrieveAllMenus(string userName)
        {
            if (string.IsNullOrEmpty(userName)) throw new ArgumentNullException(nameof(userName));

            var db = DbAccess;
            var order = db.Provider.EscapeSqlIdentifier("Order");
            return db.Fetch<BootstrapMenu>($"select n.ID, n.ParentId, n.Name, n.{order}, n.Icon, n.Url, n.Category, n.Target, n.IsResource, n.Application, d.Name as CategoryName, ln.Name as ParentName from Navigations n inner join Dicts d on n.Category = d.Code and d.Category = @Category and d.Define = 0 left join Navigations ln on n.ParentId = ln.ID inner join (select nr.NavigationID from Users u inner join UserRole ur on ur.UserID = u.ID inner join NavigationRole nr on nr.RoleID = ur.RoleID where u.UserName = @UserName union select nr.NavigationID from Users u inner join UserGroup ug on u.ID = ug.UserID inner join RoleGroup rg on rg.GroupID = ug.GroupID inner join NavigationRole nr on nr.RoleID = rg.RoleID where u.UserName = @UserName union select n.ID from Navigations n where EXISTS (select UserName from Users u inner join UserRole ur on u.ID = ur.UserID inner join Roles r on ur.RoleID = r.ID where u.UserName = @UserName and r.RoleName = @RoleName)) nav on n.ID = nav.NavigationID", new { UserName = userName, Category = "菜单", RoleName = "Administrators" });
        }

        /// <summary>
        /// 获得指定用户名可访问的所有菜单集合
        /// </summary>
        /// <param name="userName">当前用户名</param>
        /// <returns>未层次化的菜单集合</returns>
        public static IEnumerable<BootstrapMenu> RetrieveAllMenusWithCache(string userName) => CacheManager.GetOrAdd($"{RetrieveMenusAll}-{userName}", key => RetrieveAllMenus(userName), RetrieveMenusAll);

        /// <summary>
        /// 获得指定用户名与当前应用的所有层次化菜单
        /// </summary>
        /// <param name="userName">当前用户名</param>
        /// <param name="activeUrl">设置此菜单状态为 active</param>
        /// <param name="appId">App 应用ID 默认为 0 表示后台管理程序</param>
        /// <returns>带层次化的菜单集合</returns>
        public static IEnumerable<BootstrapMenu> RetrieveAppCascadeMenus(string userName, string activeUrl, string appId = "0") => CascadeMenus(RetrieveAppMenus(userName, appId), activeUrl);

        /// <summary>
        /// 获得指定用户名与当前应用的所有菜单
        /// </summary>
        /// <param name="userName">当前用户名</param>
        /// <param name="appId">App 应用ID 默认为 0 表示后台管理程序</param>
        /// <returns>未层次化的菜单集合</returns>
        public static IEnumerable<BootstrapMenu> RetrieveAppMenus(string userName, string appId = "0") => RetrieveAllMenusWithCache(userName).Where(m => m.Category == "1" && m.IsResource == 0 && m.Application == appId);

        /// <summary>
        /// 获得可访问指定菜单的所有角色集合
        /// </summary>
        /// <param name="url">指定菜单地址</param>
        /// <param name="appId">App 应用ID 默认为 0 表示后台管理程序</param>
        /// <returns>角色集合</returns>
        public static IEnumerable<string> RetrieveRolesByUrl(string url, string appId = "0")
        {
            if (string.IsNullOrEmpty(url)) throw new ArgumentNullException(nameof(url));

            var ret = DbAccess.Fetch<string>("select distinct r.RoleName from Roles r inner join NavigationRole nr on r.ID = nr.RoleID inner join Navigations n on nr.NavigationID = n.ID and n.Application = @AppId and n.Url like @Url", new { Url = $"{url}%", AppId = appId });
            if (ret.Count == 0) ret.Add("Administrators");
            return ret;
        }

        /// <summary>
        /// 获得可访问指定菜单的所有角色集合 内部缓存 Key 为 RetrieveRolesByUrlDataKey = "RoleHelper-RetrieveRolesByUrl"
        /// </summary>
        /// <param name="url">指定菜单地址</param>
        /// <returns>角色集合</returns>
        public static IEnumerable<string> RetrieveRolesByUrlWithCache(string url) => CacheManager.GetOrAdd($"{RetrieveRolesByUrlDataKey}-{url}", key => RetrieveRolesByUrl(url), RetrieveRolesByUrlDataKey);

        /// <summary>
        /// 获得指定用户所属的所有角色
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>角色集合</returns>
        public static IEnumerable<string> RetrieveRolesByUserName(string userName) => string.IsNullOrEmpty(userName) ? new List<string>() : DbAccess.Fetch<string>($"select r.RoleName from Roles r inner join UserRole ur on r.ID=ur.RoleID inner join Users u on ur.UserID = u.ID and u.UserName = @0 union select r.RoleName from Roles r inner join RoleGroup rg on r.ID = rg.RoleID inner join {DbAccess.Provider.EscapeSqlIdentifier("Groups")} g on rg.GroupID = g.ID inner join UserGroup ug on ug.GroupID = g.ID inner join Users u on ug.UserID = u.ID and u.UserName=@0", userName);

        /// <summary>
        /// 获得指定用户所属的所有角色 内部缓存 Key 为 RetrieveRolesByUserNameDataKey = "RoleHelper-RetrieveRolesByUserName"
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>角色集合</returns>
        public static IEnumerable<string> RetrieveRolesByUserNameWithCache(string userName) => CacheManager.GetOrAdd($"{RetrieveRolesByUserNameDataKey}-{userName}", key => RetrieveRolesByUserName(userName), RetrieveRolesByUserNameDataKey);

        /// <summary>
        /// 获得指定用户所属的所有部门
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>部门集合</returns>
        public static IEnumerable<BootstrapGroup> RetrieveGroupsByUserName(string userName) => string.IsNullOrEmpty(userName) ? new List<BootstrapGroup>() : DbAccess.Fetch<BootstrapGroup>($"select g.Id, g.GroupCode, g.GroupName from {DbAccess.Provider.EscapeSqlIdentifier("Groups")} g inner join UserGroup ug on g.ID = ug.GroupID inner join Users u on ug.UserID = u.ID where UserName = @0 union select g.Id, g.GroupCode, g.GroupName from {DbAccess.Provider.EscapeSqlIdentifier("Groups")} g inner join RoleGroup rg on g.ID = rg.GroupID inner join UserRole ur on rg.RoleID = ur.RoleID inner join Users u on ur.UserID = u.ID where UserName = @0 union select g.Id, g.GroupCode, g.GroupName from {DbAccess.Provider.EscapeSqlIdentifier("Groups")} g where exists(select 1 from Users u inner join UserRole ur on u.ID = ur.UserID inner join Roles r on ur.RoleID = r.ID where u.UserName = @0 and r.RoleName = 'Administrators')", userName);

        /// <summary>
        /// 获得指定用户所属的所有部门 内部缓存 Key 为 RetrieveGroupsByUserNameDataKey = "GroupHelper-RetrieveGroupsByUserName"
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>部门集合</returns>
        public static IEnumerable<BootstrapGroup> RetrieveGroupsByUserNameWithCache(string userName) => CacheManager.GetOrAdd($"{RetrieveGroupsByUserNameDataKey}-{userName}", key => RetrieveGroupsByUserName(userName), RetrieveGroupsByUserNameDataKey);

        /// <summary>
        /// 获得所有部门数据
        /// </summary>
        /// <returns></returns>
        public static IEnumerable<BootstrapGroup> RetrieveGroups() => DbAccess.Fetch<BootstrapGroup>();

        /// <summary>
        /// 获得所有部门数据 内部缓存 Key 为 RetrieveGroupsDataKey = "GroupHelper-RetrieveGroups";
        /// </summary>
        /// <returns></returns>
        public static IEnumerable<BootstrapGroup> RetrieveGroupsWithCache() => CacheManager.GetOrAdd(RetrieveGroupsDataKey, key => RetrieveGroups());

        /// <summary>
        /// 获得指定用户所属的所有授权 App
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>应用程序集合</returns>
        public static IEnumerable<string> RetrieveAppsByUserName(string userName) => string.IsNullOrEmpty(userName) ? new List<string>() : DbAccess.Fetch<string>($"select d.Code from Dicts d inner join RoleApp ra on d.Code = ra.AppId inner join (select r.Id from Roles r inner join UserRole ur on r.ID = ur.RoleID inner join Users u on ur.UserID = u.ID where u.UserName = @0 union select r.Id from Roles r inner join RoleGroup rg on r.ID = rg.RoleID inner join {DbAccess.Provider.EscapeSqlIdentifier("Groups")} g on rg.GroupID = g.ID inner join UserGroup ug on ug.GroupID = g.ID inner join Users u on ug.UserID = u.ID where u.UserName = @0) r on ra.RoleId = r.ID union select Code from Dicts where Category = @1 and exists(select r.ID from Roles r inner join UserRole ur on r.ID = ur.RoleID inner join Users u on ur.UserID = u.ID where u.UserName = @0 and r.RoleName = @2 union select r.ID from Roles r inner join RoleGroup rg on r.ID = rg.RoleID inner join {DbAccess.Provider.EscapeSqlIdentifier("Groups")} g on rg.GroupID = g.ID inner join UserGroup ug on ug.GroupID = g.ID inner join Users u on ug.UserID = u.ID where u.UserName = @0 and r.RoleName = @2)", userName, "应用程序", "Administrators");

        /// <summary>
        /// 获得指定用户所属的所有授权 App 内部缓存 Key 为 RetrieveAppsByUserNameDataKey = "AppHelper-RetrieveAppsByUserName"
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>应用程序集合</returns>
        public static IEnumerable<string> RetrieveAppsByUserNameWithCache(string userName) => CacheManager.GetOrAdd($"{RetrieveAppsByUserNameDataKey}-{userName}", key => RetrieveAppsByUserName(userName), RetrieveAppsByUserNameDataKey);

        /// <summary>
        /// 获得系统所有字典表
        /// </summary>
        /// <returns>字典表数据集合</returns>
        public static IEnumerable<BootstrapDict> RetrieveDicts() => DbAccess.Fetch<BootstrapDict>("select * from Dicts");

        /// <summary>
        /// 获得系统所有字典表 内部缓存 Key 为 RetrieveDictsDataKey = "BootstrapDict-RetrieveDicts"
        /// </summary>
        /// <param name="category">字典分类 默认获取所有分类</param>
        /// <returns>字典表数据集合</returns>
        public static IEnumerable<BootstrapDict> RetrieveDictsWithCache(string category = null)
        {
            var dicts = CacheManager.GetOrAdd(RetrieveDictsDataKey, key => RetrieveDicts());
            if (!string.IsNullOrEmpty(category)) dicts = dicts.Where(d => d.Category.Equals(category, StringComparison.OrdinalIgnoreCase));
            return dicts;
        }

        /// <summary>
        /// 获取当前应用程序关联的个人中心地址
        /// </summary>
        /// <param name="appId">App 应用ID 默认为 0 表示后台管理程序</param>
        /// <returns></returns>
        public static string RetrieveProfilesUrl(string appId = "0")
        {
            return RetrieveAppName("个人中心地址", appId);
        }

        /// <summary>
        /// 获取当前应用程序关联的系统设置地址
        /// </summary>
        /// <param name="appId">App 应用ID 默认为 0 表示后台管理程序</param>
        /// <returns></returns>
        public static string RetrieveSettingsUrl(string appId = "0")
        {
            return RetrieveAppName("系统设置地址", appId);
        }

        /// <summary>
        /// 获取当前应用程序关联的系统通知地址
        /// </summary>
        /// <param name="appId">App 应用ID 默认为 0 表示后台管理程序</param>
        /// <returns></returns>
        public static string RetrieveNotisUrl(string appId = "0")
        {
            return RetrieveAppName("系统通知地址", appId);
        }

        /// <summary>
        /// 获取当前应用程序关联的网站标题
        /// </summary>
        /// <param name="appId">App 应用ID 默认为 0 表示后台管理程序</param>
        /// <returns></returns>
        public static string RetrieveTitle(string appId = "0")
        {
            return RetrieveAppName("网站标题", appId);
        }

        /// <summary>
        /// 获取当前应用程序关联的网站页脚
        /// </summary>
        /// <param name="appId">App 应用ID 默认为 0 表示后台管理程序</param>
        /// <returns></returns>
        public static string RetrieveFooter(string appId = "0")
        {
            return RetrieveAppName("网站页脚", appId);
        }

        private static string RetrieveAppName(string name, string appId = "0", string defaultValue = "未设置")
        {
            var dicts = RetrieveDictsWithCache();
            var platName = dicts.FirstOrDefault(d => d.Category == "应用程序" && d.Code == appId)?.Name;
            return dicts.FirstOrDefault(d => d.Category == platName && d.Name == name)?.Code ?? $"{name}{defaultValue}";
        }

        /// <summary>
        /// 获得网站设置中的当前样式
        /// </summary>
        /// <returns></returns>
        public static string RetrieveActiveTheme()
        {
            var theme = RetrieveDictsWithCache().Where(d => d.Name == "使用样式" && d.Category == "当前样式" && d.Define == 0).FirstOrDefault()?.Code;
            return theme == null ? string.Empty : theme.Equals("site.css", StringComparison.OrdinalIgnoreCase) ? string.Empty : theme;
        }

        /// <summary>
        /// 获取头像路径 默认 ~/images/uploader/
        /// </summary>
        /// <returns></returns>
        public static string RetrieveIconFolderPath(string defaultPath = "~/images/uploader/") => RetrieveDictsWithCache().FirstOrDefault(d => d.Name == "头像路径" && d.Category == "头像地址" && d.Define == 0)?.Code ?? defaultPath;

        /// <summary>
        /// 获取系统图床路径 默认 http://images.sdgxgz.com/
        /// </summary>
        /// <returns></returns>
        public static string RetrieveImagesLibUrl(string defaultUrl = "http://images.sdgxgz.com/") => RetrieveDictsWithCache().FirstOrDefault(d => d.Name == "验证码图床" && d.Category == "系统设置" && d.Define == 0)?.Code ?? defaultUrl;

        /// <summary>
        /// 获得指定用户名的用户信息
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>BootstrapUser 实例</returns>
        public static BootstrapUser RetrieveUserByUserName(string userName)
        {
            if (string.IsNullOrEmpty(userName)) throw new ArgumentNullException(nameof(userName));

            var user = DbAccess.SingleOrDefault<BootstrapUser>("select UserName, DisplayName, Icon, Css, App from Users where ApprovedTime is not null and UserName = @0", userName);
            if (user != null)
            {
                if (string.IsNullOrEmpty(user.Icon))
                {
                    user.Icon = "default.jpg";
                }

                if (string.IsNullOrEmpty(user.App))
                {
                    user.App = "0";
                }
            }
            return user;
        }

        /// <summary>
        /// 获得指定用户名的用户信息
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>BootstrapUser 实例</returns>
        public static BootstrapUser RetrieveUserByUserNameWithCache(string userName) => CacheManager.GetOrAdd(string.Format("{0}-{1}", RetrieveUsersByNameDataKey, userName), key => RetrieveUserByUserName(userName), RetrieveUsersByNameDataKey);

        /// <summary>
        /// 通过当前用户名与指定菜单路径获取此菜单下所有授权按钮集合 (userName, url, auths) => bool
        /// </summary>
        /// <param name="menus">当前授权可用的菜单集合</param>
        /// <param name="url">资源按钮所属菜单</param>
        /// <param name="auths">资源授权码</param>
        /// <returns></returns>
        public static bool AuthorizateButtons(IEnumerable<BootstrapMenu> menus, string url, string auths)
        {
            var activeMenu = menus.FirstOrDefault(m => m.Url.Equals(url, StringComparison.OrdinalIgnoreCase));
            if (activeMenu == null) return false;

            var authorKeys = menus.Where(m => m.ParentId == activeMenu.Id && m.IsResource == 2).Select(m => m.Url);
            var keys = auths.SpanSplitAny(",. ;", StringSplitOptions.RemoveEmptyEntries);
            return keys.Any(m => authorKeys.Any(k => k == m));
        }

        private static IDatabase DbAccess
        {
            get
            {
                if (Mappers.GetMapper(typeof(BootstrapDict), null) == null) Mappers.Register(typeof(BootstrapDict).Assembly, new BootstrapAdminConventionMapper());
                return DbManager.Create("ba");
            }
        }
    }
}
